/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBlayer;

import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Engel
 */
public class DBCustomer implements IFDBCustomer
{

	private Connection con;

	public DBCustomer()
	{
	con = DBConnection.getInstance().getDBcon();
	}

	public ArrayList<Customer> getAllCustomers(boolean retrieveAssociation)
    {
        return miscWhere("", retrieveAssociation);
    }

	public Customer findCustomer(int ID, boolean retrieveAssociation)
    {
        String wClause = "  customerID = '" + ID + "'";
        return singleWhere(wClause, retrieveAssociation);
    }

	public Customer searchCustomerName(String name, boolean retrieveAssociation)
    {
        String wClause = "name like '%" + name + "%'";
        System.out.println("Searchproduct " + wClause);
        return singleWhere(wClause, retrieveAssociation);
    }

	public int insertCustomer(Customer customer) 
	{
	int rc = -1;
    String query="INSERT INTO persiCustomer(name, address, odeZipC, phoneno)  VALUES('"+
	  		customer.getName()  + "','"  +
			customer.getAddress() + "','" +
			customer.getCity().getOdeZipC() + "','" +
			customer.getPhoneno() + "')";

      System.out.println("insert : " + query);
      try{ // insert new customer
            Statement stmt = con.createStatement();
	 	  	stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
		}//end try
	 	catch(Exception ex){
	 	  	System.out.println("Insert exception in persiCustomer db: "+ex);
	   }
	   return(rc);
	}

	public void delete(int id)
	{
               int rc=-1;

	  	String query="DELETE FROM persiCustomer WHERE customerID = '" +
				id + "'";
                System.out.println(query);
	  	try{ // delete from customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in persiCustomer db: "+ex);
   	        }
	}

	public int updateCustomer(Customer customer)
	{
		Customer customerObj  = customer;
		int rc=-1;

		String query="UPDATE persiCustomer SET "+
                        "name ='" + customerObj.getName() + "', " +
		    	"address ='" + customerObj.getAddress() + "', " +
                        "odeZipC ='" + customerObj.getCity().getOdeZipC() + "', " +
                        "phoneNo ='" + customerObj.getPhoneno() + "' " +
		        " WHERE customerID = '"+ customerObj.getID() + "'";

                System.out.println("Update query:" + query);
  		try{ // update customer
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in customer db: "+ex);
	  	}
		return(rc);
    }

	private ArrayList<Customer> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Customer> list = new ArrayList<Customer>();

	    String query =  buildQuery(wClause);

            try{ // read the persiCustomer from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

		int snr=0;
		while( results.next() ){
	     	 Customer customerObj = new Customer();
		 customerObj = buildCustomer(results);
                 list.add(customerObj);
		}//end while
                 stmt.close();
                 if(retrieveAssociation)
                 {   //The city is to be build as well
                     for(Customer customerObj : list){
			IFDBCity dbCityObj = new DBCity();
                        City cityObj = dbCityObj.findCity(customerObj.getCity().getOdeZipC());
                        System.out.println("City is seleceted ");
			customerObj.setCity(cityObj);
                     }
                 }//end if

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	//Singelwhere is used when we only select one customer
	private Customer singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Customer customerObj = new Customer();

	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the persiCustomer from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() ){
                            customerObj = buildCustomer(results);
                            //assocaition is to be build
                            stmt.close();
                            if(retrieveAssociation)
                            {   //The city is to be build as well
                            IFDBCity dbCityObj = new DBCity();
                            City cityObj = dbCityObj.findCity(customerObj.getCity().getOdeZipC());
                            System.out.println("City is seleceted ");
                            customerObj.setCity(cityObj);
                            }
			}
                        else{ //no employee was found
                            customerObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return customerObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT customerID, name, address, odeZipC, phoneNo FROM persiCustomer";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}

	private Customer buildCustomer(ResultSet results)
      {   Customer customerObj = new Customer();
		  IFDBCity dbCityObj = new DBCity();
          try{ // the columns from the table persiCustomer are used
                customerObj.setID(results.getInt("customerID"));
                customerObj.setName(results.getString("name"));
                customerObj.setAddress(results.getString("address"));
                customerObj.setCity(new City(results.getInt("odeZipC")));
                customerObj.setPhoneno(results.getInt("phoneNo"));
          }
         catch(Exception e)
         {
             System.out.println("error in building the customer object");
         }
         return customerObj;
      }
}
